ШАГ 5 - Функции первой группы

Эти упражнения охватывают использование функций не только во фразе SELЕСТ, но и во фразах WHERE и ORDER. BY. Если результат содержит псевдонимы -- используйте их во фразе SELЕСТ команды SQL.

Функции в Oracle делятся на 2 группы.

Символьные функции

LOWER(столбец|значение)-- перевод на нижний регистр;
UPPER(столбец|значение)-- перевод на верхний регистр;
INITCAP(столбец|значение)-- первая буква прописная, остальные строчные;
CONCAT(строка1, строка2)-- конкатенация строк (альтернатива ||);
LPAD(столбец|литерал, n,»строка»)-- используя «строку» в качестве заполнителя заполняет столбец или литерал слева до n символов;
RPAD(столбец|литерал, n,»строка»)-- то же, но заполняет справа;
SUBSTR(столбец|литерал,номер,n)-- возвращает фрагмент столбца или литерала длиной в n символов, начиная с позиции номер; если число n не задано, возвращается фрагмент с позиции номер и до конца;
INSTR(столбец|литерал, «строка», [номер, n])-- определяет начальную позицию в кратком варианте первого, в длинном n-го вхождения строки в значение столбца или литерала;
LTRIM(столбец|литерал,«символ(ы)»),
RTRIM(столбец|литерал,«символ(ы)»)-- удаляет слева (справа) все головные(хвостовые) символы, перечисленные в наборе -символ(ы); если последний аргумент не задан удаляются все головные (хвостовые) пробелы;
SOUNDEX(столбец|литерал)-- только для английского языка; возвращает фонетическое представление слов(а) в значении столбца или литерала;
LENGTH(столбец|литерал)-- возвращает число символов в значении;
TRANSLATE(столбец|литерал,символ(ы)1,символ(ы)2)-- заменяет символы из набора символ(ы)1 на соответствующие символы из набора символ(ы)2 ; если соответствующий символ в наборе символ(ы)2 не задан, то символ из символ(ы)1 удаляется;
REPLACE(столбец|литерал, строка_образец,строка_замены) -- преобразует значение столбца или литерала, заменяя в нем строку_образец при каждом ее вхождении на строку_замены; Если строка_замены не задана то при каждом вхождении удаляется строка_образец;

Числовые функции

ROUND(столбец|значение, n)-- округляет столбец, выражение или значение до n десятичных знаков; если же n не указано, округление до целых чисел; при n<0 округление до n-го знака слева от десятичной точки;
TRUNC(столбец|значение, n)-- усечение до n десятичных знаков; при отсутствии n дробная часть отбрасывается. при n<0 n знаков слева от десятичной точки заменяются на 0;
CEIL(столбец|значение)-- наименьшее целое превышающее аргумент или равное ему;
FLOOR(столбец|значение)-- наибольшее целое меньшее аргумента или равное ему;
POWER(столбец|значение, n)-- возведение в степень n;
EXP(n)-- экспоненциал;
SQRT(столбец|значение)-- корень квадратный; если аргумент <0 или NULL, возвращается NULL;
SIGN(столбец|значение)-- знаковая функция (-1,0+1); часто используют для сравнения значений;
ABS(столбец|значение)-- модуль;
MOD(значение1, значение2)-- остаток от деления значения1 на значение2;
LOG(m,n)-- логарифм от n по основанию m;
SIN(n), SINH(n), TAN(n), TANH(n), COS(n), COSH(n);

Для строчных функций допускается вычисление функции от функции. Глубина вложенности любая. Вычисление вложенных функций начинается с самого нижнего уровня. Например, для определения количества букв ‘S’ в значении столбца можно использовать один из запросов:

SELECT DNAME, LENGTH(DNAME), LENGTH(DNAME)- LENGTH(TRANSLATE(DNAME, 'AS','A')) FROM DEPT;

или

SELECT DNAME, LENGTH(DNAME), LENGTH(DNAME) - LENGTH(REPLACE(DNAME, 'S')) FROM DEPT;

Упражнения

1. Перечислим имена сотрудников и их оклад, увеличенный на 15% и округленный до целого числа долларов.

SELЕСТ DEPTNO, ЕNАМЕ, ROUND (SAL* 1.15) PCTSAL FRОМ EMP;

В окне SQL*Plus Вы должны получить следующие значения:

DEPTNO ENAME ROUND(SAL*1.15)
------ ----- --------------
20     SMITH            920
30     ALLEN           1840
30     WARD            1438
20     JONES           3421
30     MARTIN          1438
30     BLAKE           3278
..     ....            ....

2. Выведем фамилии сотрудников с их должностью в скобках рядом. Должность должна начинаться с заглавной буквы. Все должно находиться в одном столбце.

SELЕСТ ENAME || ’(‘ || initcap(job) || ')' EMPLOYEE FRОМ EMP;

В окне SQL*Plus Вы должны получить следующие значения:

EMPLOYEE
---------------
SMITH(Clerk)
ALLEN(Salesman)
WARD(Salesman)
JONES(Manager)
MARTIN(Salesman)
BLAKE(Manager)
...

3. Напишем команду SELECT для отбора сотрудников по должности, которая будет задаваться вами во время выполнения. Сделаем это так, чтобы должность можно было вводить как строчными, так и прописными буквами.

SELЕСТ * FRОМ EMP WHERE UPPER (JOB) = UPPER('&JOB');

После ввода должности MANAGER В окне SQL*Plus Вы должны получить следующие значения:

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
----- ---------- --------- ---------- --------- ---------- ---------- ----------
 7566 JONES      MANAGER         7839 02-APR-81       2975                    20
 7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
 7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

4. Известно, что в отделе номер 30 некоторые из продавцов -- женщины (поэтому для них название SALESMAN не уместно). Вот что мы можем получить запросом ниже.

SELЕСТ ENAME, DEPTNO, INITCAP(REPLACE(JOB,'SALESMAN', 'SALESPERSON')) JOB FRОМ EMP WHERE DEPTNO = 30;

В окне SQL*Plus Вы должны получить следующие значения:

ENAME          DEPTNO JOB
---------- ---------- -------------
ALLEN              30 Salesperson
WARD               30 Salesperson
MARTIN             30 Salesperson
BLAKE              30 Manager
TURNER             30 Salesperson
JAMES              30 Clerk
Автор: Michael Nemtsev aka 'LaFlour'

Hosted by uCoz